using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Tiago.Data
{
    public class DBWrapper
    {
        /// <summary>
        /// Wrapper for a set of results
        /// </summary>
        public class Results
        {
            private DataTable table;

            /// <summary>
            /// Constructs the set of results from an open DataAdapter
            /// </summary>
            /// <param name="reader">An open DataAdapter with the results of a query</param>
            public Results(OleDbDataAdapter adapter)
            {
                this.table = new DataTable();
                adapter.Fill(this.table);
                adapter.Dispose();
            }

            /// <summary>
            /// Returns an item from the result set
            /// </summary>
            /// /// <param name="column">Name of the column in the result set</param>
            /// <param name="row">Index of the row in the result set</param>
            /// <returns>The item in the cell indexed by the given column and row, or null if doesn't exist</returns>
            public string Get(string column,int row)
            {
                return this.table.Rows[row][column].ToString();
            }

            /// <summary>
            /// Returns the number of rows in the result object
            /// </summary>
            /// <returns>Number of rows in the result set</returns>
            public int getNumberRows()
            {
                return this.table.Rows.Count;
            }
        }

        // The database's source
        private string data_source;
        // The database's username
        private string username;
        // The database's password
        private string password;
        // The database connection
        private System.Data.OleDb.OleDbConnection connection;

        /// <summary>
        /// Initializes the database wrapper class
        /// </summary>
        /// <param name="path">The path to the data source</param>
        /// <param name="username">The username that will be used to connect to the database</param>
        /// <param name="password">The password for the given username</param>
        public DBWrapper(string path, string username, string password)
        {
            this.username = username;
            this.password = password;
            this.data_source = path;
            this.connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+this.data_source
            +";User Id="+this.username+";Password="+this.password+";");
            try
            {
                this.connection.Open();
            }catch
            {
            }
        }

        /// <summary>
        /// Opens the database connection
        /// </summary>
        private void Open()
        {
            if (this.connection.State == ConnectionState.Closed)
                this.connection.Open();
            else
                if (this.connection.State == ConnectionState.Broken)
                {
                    this.connection.Close();
                    this.connection.Open();
                }
        }

        /// <summary>
        /// Closes the database connection
        /// </summary>
        public void Close()
        {
            if(this.connection.State != ConnectionState.Closed)
                this.connection.Close();
        }

        /// <summary>
        /// Transforms a list of field = value keys into a WHERE string to be used in SQL queries
        /// </summary>
        /// <param name="keys">List of field and key associations in the field1=value1,field2=value2... format</param>
        /// <returns>WHERE string to be used in an SQL query</returns>
        private string formatWhereString(string keys)
        {
            string ret = "";
            string[] key_tokens = keys.Split(new char[1] { '|' });
            foreach (string key in key_tokens)
            {
                string[] operands = key.Split(new char[1] { '=' });
                string field = operands[0].Trim();
                string value = operands[1].Trim();
                if (value[0] == '\'' && value[value.Length - 1] == '\'')
                {
                    ret += field + " = " + value + " AND ";
                }
                else
                {
                    try
                    {
                        DateTime time = DateTime.Parse(value);
                        ret += "datevalue(" + field + ") = datevalue('" + value + "') AND ";
                    }
                    catch
                    {
                        try
                        {
                            int.Parse(value);
                            ret += field + " = " + value + " AND ";
                        }
                        catch
                        {
                            ret += field + " = '" + value + "' AND ";
                        }
                    }
                }
            }
            
            return ret.Substring(0, ret.Length - 5);
        }

        /// <summary>
        /// Returns the number of rows in a table that match a given condition
        /// </summary>
        /// <param name="table">The table where to count the rows</param>
        /// <param name="filter">The filter the rows must match</param>
        /// <returns>The number of rows in the table that match the given condition</returns>
        public int CountRows(string table, string filter)
        {
            return Select("id", table, filter).getNumberRows();
        }

        /// <summary>
        /// Performs a SELECT SQL query on the database
        /// </summary>
        /// <param name="fields">The fields that the user wants to retrieve separated by a comma</param>
        /// <param name="tables">The tables the user wants to query</param>
        /// <returns>An object with the query results</returns>
        public DBWrapper.Results Select(string fields, string tables)
        {
            return Select(fields,tables,"");
        }

        /// <summary>
        /// Performs a SELECT SQL query on the database
        /// </summary>
        /// <param name="fields">The fields that the user wants to retrieve separated by a comma</param>
        /// <param name="tables">The tables the user wants to query</param>
        /// <param name="filter">The filter expression for the query</param>
        /// <returns>An object with the query results</returns>
        public DBWrapper.Results Select(string fields, string tables, string filter)
        {
            return Select(fields, tables, filter, "");
        }

        /// <summary>
        /// Performs a SELECT SQL query on the database
        /// </summary>
        /// <param name="fields">The fields that the user wants to retrieve separated by a comma</param>
        /// <param name="tables">The tables the user wants to query</param>
        /// <param name="filter">The filter expression for the query</param>
        /// <param name="order">The order expression for the query</param>
        /// <returns>An object with the query results</returns>
        public DBWrapper.Results Select(string fields, string tables, string filter, string order)
        {
            List<string> results = new List<string>();
            string query = "SELECT " + fields + " FROM " + tables + ((filter == "") ? "" : " WHERE " + filter) + ((order == "") ? "" : " ORDER BY " + order) + ";";
            OleDbDataAdapter adapter = new OleDbDataAdapter(query, this.connection);
            Results results_obj = new Results(adapter);
            return results_obj;
        }

        /// <summary>
        /// Inserts a row into a table
        /// </summary>
        /// <param name="table">The table's name</param>
        /// <param name="keys">A list of fields and values in the format: field1=value1,field2=value2,...</param>
        /// <returns>The id associated with the entry (table must have "id" field, else it returns null)</returns>
        public string Insert(string table, string keys)
        {
            List<string> fields = new List<string>();
            List<string> values = new List<string>();
            string[] key_tokens = keys.Split(new char[1] { '|' });
            foreach (string key in key_tokens)
            {
                string[] tokens = key.Trim().Split(new char[1] { '=' });
                fields.Add(tokens[0].Trim());
                values.Add(tokens[1].Trim());
            }

            string query = "INSERT INTO " + table + "(";
            foreach (string field in fields)
                query += field + ",";
            query = query.Substring(0,query.Length-1) + ") VALUES(";
            foreach (string value in values)
                query += "'" + value + "',";
            query = query.Substring(0, query.Length - 1) + ");";

            new OleDbCommand(query, this.connection).ExecuteNonQuery();

            string id = Select("id", table, formatWhereString(keys)).Get("id", 0);
            if (id == "")
                return null;
            return id;
        }

        /// <summary>
        /// Deletes entries from a table
        /// </summary>
        /// <param name="table">The table where the deletion is going to take place</param>
        /// <param name="filter">The criteria that the candidate rows for deletion must have</param>
        /// <returns>The number of deleted rows</returns>
        public int Delete(string table, string filter)
        {
            string query = "DELETE FROM " + table + " WHERE " + filter + ";";
            return new OleDbCommand(query, this.connection).ExecuteNonQuery();
        }

        /// <summary>
        /// Updates data in table
        /// </summary>
        /// <param name="table">The table's name</param>
        /// <param name="updates">The updates that are going to take place</param>
        /// <returns>The number of updated rows</returns>
        public int Update(string table, string updates)
        {
            string query = "UPDATE " + table + " SET " + updates + ";";
            return new OleDbCommand(query, this.connection).ExecuteNonQuery();
        }

        /// <summary>
        /// Updates data in table
        /// </summary>
        /// <param name="table">The table's name</param>
        /// <param name="updates">The updates that are going to take place</param>
        /// <param name="filter">The criteria that the candidate rows for update must have</param>
        /// <returns>The number of updated rows</returns>
        public int Update(string table, string updates, string filter)
        {
            string query = "UPDATE " + table + " SET " + updates + " WHERE " + filter + ";";
            return new OleDbCommand(query, this.connection).ExecuteNonQuery();
        }
    }
}
